﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using Npgsql;

namespace PostDbNet
{
    public class PostSqlUtil : IDisposable
    {
        private NpgsqlConnection Connection;
        private String ConnectionString;

        public PostSqlUtil()
        {
            ConnectionString = ConfigurationManager.ConnectionStrings["postgresql"].ConnectionString;
        }

        public PostSqlUtil(string pStr)
        {
            ConnectionString = ConfigurationManager.ConnectionStrings[pStr].ConnectionString;
        }

        ~PostSqlUtil()
        {
            try
            {
                if (Connection != null)
                    Connection.Close();
            }
            catch { }
            try
            {
                Dispose();
            }
            catch { }
        }

        public NpgsqlConnection pConnection
        {
            get
            {
                return Connection;
            }
            set
            {
                Connection = value;
            }
        }

        /// ***********************************
        /// Open Connection
        /// ***********************************
        public void Open()
        {
            if (Connection == null)
            {
                Connection = new NpgsqlConnection(ConnectionString);
            }
            if (Connection.State.Equals(ConnectionState.Closed))
            {
                Connection.Open();
            }
        }
        /// ***********************************
        /// Close Connection
        /// ***********************************
        public void Close()
        {
            if (Connection != null)
                Connection.Close();
        }
        /// ***********************************
        /// Dispose Connection
        /// ***********************************
        public void Dispose()
        {
            // Ensure Connection Closed
            if (Connection != null)
            {
                Connection.Dispose();
                Connection = null;
            }
        }

        /// **********************************************************************
        /// Sql Executor
        /// <param name="SqlString">Sql语句</param>
        /// <returns>
        /// 对Update、Insert、Delete为影响到的行数，其他情况为-1
        /// </returns>
        /// **********************************************************************
        public int ExecuteSQL(String SqlString)
        {
            int count = -1;
            Open();
            try
            {
                NpgsqlCommand cmd = new NpgsqlCommand(SqlString, Connection);
                count = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                count = -2;
            }
            finally
            {
                Close();
            }

            return count;
        }

        /// ***************************************************
        /// 获取数据:返回一个DataSet
        /// <param name="SqlString">Sql语句</param>
        /// <returns>DataSet</returns>
        /// ***************************************************
        public DataSet GetDataSet(String SqlString)
        {
            Open();
            NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(SqlString, Connection);
            DataSet dataset = new DataSet();
            adapter.Fill(dataset);
            Close();

            return dataset;
        }

        /// **************************************************
        /// 获取数据:返回一个SqlDataReader 
        /// （调用后主意调用SqlDataReader.Close()）
        /// <param name="SqlString">Sql语句</param>
        /// <returns>SqlDataReader</returns>
        /// **************************************************
        public NpgsqlDataReader GetDataReader(String SqlString)
        {
            Open();
            NpgsqlCommand cmd = new NpgsqlCommand(SqlString, Connection);

            return cmd.ExecuteReader();
        }

        /// ***************************************************
        /// Execute a procedure of params
        /// <param>SqlParameter[]</param>
        /// <returns>是否成功</returns>
        /// ***************************************************
        public bool ExecuteSpParam(string SpName, NpgsqlParameter[] Prams)
        {
            bool success = true;

            Open();

            NpgsqlCommand cmd = new NpgsqlCommand();
            NpgsqlTransaction trans = Connection.BeginTransaction();
            cmd.Connection = Connection;
            cmd.Transaction = trans;
            cmd.CommandText = SpName;
            cmd.CommandType = CommandType.StoredProcedure;

            try
            {
                foreach (NpgsqlParameter pram in Prams)
                {
                    cmd.Parameters.Add(pram);
                }

                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch
            {
                success = false;
                trans.Rollback();
            }
            finally
            {
                Close();
            }

            return success;
        }

        /// ***************************************************
        /// Execute a sql of params
        /// <param>SqlParameter[]</param>
        /// <returns>是否成功</returns>
        /// ***************************************************
        public int ExecuteSqlParam(string Sql, NpgsqlParameter[] Prams)
        {
            int count = -1;
            Open();

            NpgsqlCommand cmd = new NpgsqlCommand();
            cmd.Connection = Connection;
            cmd.CommandText = Sql;

            try
            {
                foreach (NpgsqlParameter pram in Prams)
                {
                    cmd.Parameters.Add(pram);
                }

                cmd.ExecuteNonQuery();
            }
            catch
            {
                count = -2;
            }
            finally
            {
                Close();
            }

            return count;
        }
    }
}
